package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.maszy.bean.Breakfast;
import com.maszy.db.DBConnection;

public class BreakfastService {
	private static PreparedStatement pst;
	private static ResultSet rs = null;
	public  int add(Breakfast obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql ="INSERT INTO [Maszy_Breakfast]([Breakfast_Name], [Breakfast_Specification], [Breakfast_Unit], [Breakfast_Dept], [Breakfast_Status], [Breakfast_Morning], [Breakfast_Afternoon], [Breakfast_Remark],[Breakfast_Sort]) VALUES(?,?,?,?,?,?, ?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {			
			pst.setString(1,obj.getProduct_name());
			pst.setString(2, obj.getSpecification());
			pst.setString(3,obj.getUnit());
			pst.setInt(4,obj.getDept_id());
			pst.setString(5,obj.getStatus());			
			pst.setInt(6, obj.getMorning_tang());
			pst.setInt(7, obj.getAfternoon_tang());
			pst.setString(8, obj.getRemark());
			pst.setInt(9, obj.getBreakfast_Sort());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}
	public int update(Breakfast obj){
		DBConnection con = new DBConnection();
		int i=0;
		String sql="UPDATE [Maszy_Breakfast] SET [Breakfast_Name]=?, [Breakfast_Specification]=?, [Breakfast_Unit]=?, [Breakfast_Dept]=?, [Breakfast_Status]=?, [Breakfast_Morning]=?, [Breakfast_Afternoon]=?, [Breakfast_Remark]=?,[Breakfast_Sort]=? WHERE [Breakfast_Id]=?";
		
		try {
			pst = con.getConnection().prepareStatement(sql);			
			pst.setString(1,obj.getProduct_name());
			pst.setString(2, obj.getSpecification());
			pst.setString(3,obj.getUnit());
			pst.setInt(4,obj.getDept_id());
			pst.setString(5,obj.getStatus());	
			pst.setInt(6, obj.getMorning_tang());
			pst.setInt(7, obj.getAfternoon_tang());
			pst.setString(8, obj.getRemark());
			pst.setInt(9, obj.getBreakfast_Sort());
			pst.setLong(10, obj.getBreakfast_id());
			
			i=pst.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}
	public int delete(Breakfast obj){
		DBConnection con = new DBConnection();
		int i=0;
		String sql="delete from  Maszy_Breakfast  where Breakfast_Id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);	
			pst.setLong(1, obj.getBreakfast_id());
			i=pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}
	public Breakfast queryById(Breakfast obj){
		DBConnection con = new DBConnection();
		String sql="select * from Maszy_Breakfast where [Breakfast_Id]=?";
		try {
			pst = con.getConnection().prepareStatement(sql);	
			pst.setLong(1, obj.getBreakfast_id());
			rs = pst.executeQuery();
			while (rs.next()) {
				obj=new Breakfast();
				obj.setBreakfast_id(rs.getLong("Breakfast_Id"));
				obj.setProduct_name(rs.getString("Breakfast_Name"));
				obj.setSpecification(rs.getString("Breakfast_Specification"));
				obj.setUnit(rs.getString("Breakfast_Unit"));
				obj.setDept_id(rs.getInt("Breakfast_Dept"));
				obj.setStatus(rs.getString("Breakfast_Status"));
				obj.setMorning_tang(rs.getInt("Breakfast_Morning"));
				obj.setAfternoon_tang(rs.getInt("Breakfast_Afternoon"));
				obj.setRemark(rs.getString("Breakfast_Remark"));
				obj.setBreakfast_Sort(rs.getInt("Breakfast_Sort"));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}
	public List<Breakfast> queryAll(){
		List<Breakfast> list=new ArrayList<Breakfast>();
		DBConnection con = new DBConnection();
		String sql="select * from Maszy_Breakfast order by Breakfast_Sort";
		try {
			pst = con.getConnection().prepareStatement(sql);				
			rs = pst.executeQuery();
	
			while (rs.next()) {
				Breakfast obj=new Breakfast();
				obj=new Breakfast();
				obj.setBreakfast_id(rs.getLong("Breakfast_Id"));
				obj.setProduct_name(rs.getString("Breakfast_Name"));
				obj.setSpecification(rs.getString("Breakfast_Specification"));
				obj.setUnit(rs.getString("Breakfast_Unit"));
				obj.setDept_id(rs.getInt("Breakfast_Dept"));
				obj.setStatus(rs.getString("Breakfast_Status"));
				obj.setMorning_tang(rs.getInt("Breakfast_Morning"));
				obj.setAfternoon_tang(rs.getInt("Breakfast_Afternoon"));
				obj.setRemark(rs.getString("Breakfast_Remark"));
				obj.setBreakfast_Sort(rs.getInt("Breakfast_Sort"));
				list.add(obj);
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
	public List<Breakfast> queryAllByStatus(){
		List<Breakfast> list=new ArrayList<Breakfast>();
		DBConnection con = new DBConnection();
		String sql="select * from Maszy_Breakfast where Breakfast_Status=1 order by Breakfast_Id asc";
		try {
			pst = con.getConnection().prepareStatement(sql);				
			rs = pst.executeQuery();
	
			while (rs.next()) {
				Breakfast obj=new Breakfast();
				obj=new Breakfast();
				obj.setBreakfast_id(rs.getLong("Breakfast_Id"));
				obj.setProduct_name(rs.getString("Breakfast_Name"));
				obj.setSpecification(rs.getString("Breakfast_Specification"));
				obj.setUnit(rs.getString("Breakfast_Unit"));
				obj.setDept_id(rs.getInt("Breakfast_Dept"));
				obj.setStatus(rs.getString("Breakfast_Status"));
				obj.setMorning_tang(rs.getInt("Breakfast_Morning"));
				obj.setAfternoon_tang(rs.getInt("Breakfast_Afternoon"));
				obj.setRemark(rs.getString("Breakfast_Remark"));
				obj.setBreakfast_Sort(rs.getInt("Breakfast_Sort"));
				list.add(obj);
			}
			
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
}
